"""
从数据库中读取员工的编号、姓名、职位、月薪和部门名称，写入Excel文件
"""
import openpyxl
import pymysql

conn = pymysql.connect(host='10.7.174.103', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    with conn.cursor() as cursor:
        cursor.execute(
            'select eno, ename, job, sal, dname from tb_emp t1 '
            'inner join tb_dept t2 on t1.dno=t2.dno'
        )
        wb = openpyxl.Workbook()
        # ws = wb.create_sheet('员工表')
        ws = wb.active
        titles = ('工号', '姓名', '职位', '月薪', '部门')
        # openpyxl操作Excel时，行和列的索引都是从1开始的
        for col_idx, col_name in enumerate(titles):
            ws.cell(1, col_idx + 1, col_name)
        for row_idx, emp_row in enumerate(cursor.fetchall()):
            for col_idx, col_value in enumerate(emp_row):
                ws.cell(row_idx + 2, col_idx + 1, col_value)
        wb.save('人力资源管理.xlsx')
except pymysql.MySQLError as err:
    print(err)
finally:
    conn.close()
